<?php
class ModelBlogPost extends Model {
		
	public function getRecentPosts() {
		$sql = "SELECT 
					bp.created,
					bpd.title,
					CONCAT(usr.firstname, ' ' ,usr.lastname) as author_name  
				FROM 
					" . DB_PREFIX . "blog_posts bp 
					LEFT JOIN " . DB_PREFIX . "blog_posts_description bpd ON (bp.id = bpd.blog_post_id) 
					LEFT JOIN " . DB_PREFIX . "user usr ON (usr.user_id = bp.modified_by)
				WHERE 
					bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
					AND  bp.published = '1'
				ORDER BY 
					bp.modified DESC,bp.created DESC 
				LIMIT 
					3
				";
		$query = $this->db->query($sql);

		return $query->rows;
	}	
	
		
	public function getTotalPosts($data) {
      	$sql = "";

		if (!empty($data['filter_title'])) {
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog_posts as bp LEFT JOIN " . DB_PREFIX . "blog_posts_description as bpd ON(bpd.blog_post_id = bp.id) WHERE  bpd.language_id = '" . (int)$this->config->get('config_language_id')."' AND  bp.published = '1'";
		}else{
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog_posts as bp WHERE 1 AND  bp.published = '1'";
		}

		if (!empty($data['filter_title'])) {
			$sql .= " AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}	
		
		
	
	public function getPostDescriptions($id) {
		$post_description_data = array();
		
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "blog_posts_description WHERE blog_post_id = '" . (int)$id . "'");

		foreach ($query->rows as $result) {
			$post_description_data[$result['language_id']] = array(
				'title'       => $result['title'],
				'description' => $result['description']
			);
		}
		
		return $post_description_data;
	}
	
	public function getTotalPostsByCategory($data) {
      	$sql = "";

		if (!empty($data['filter_title'])) {
			$sql = "SELECT 
						COUNT(*) AS total 
					FROM 
						" . DB_PREFIX . "blog_posts as bp 
						LEFT JOIN " . DB_PREFIX . "blog_posts_description as bpd ON(bpd.blog_post_id = bp.id) 
						LEFT JOIN " . DB_PREFIX . "blog_posts_to_blog_categories as bc ON(bc.blog_post_id = bp.id) 
					WHERE 
						bpd.language_id = '" . (int)$this->config->get('config_language_id')."' 
						AND  bp.published = '1' 
						AND bc.blog_category_id='".$data['category_id']."'";
		}else{
			$sql = "SELECT 
						COUNT(*) AS total 
					FROM 
						" . DB_PREFIX . "blog_posts as bp 
						LEFT JOIN " . DB_PREFIX . "blog_posts_to_blog_categories as bc ON(bc.blog_post_id = bp.id) 
					WHERE 
						bp.published = '1' 
						AND bc.blog_category_id='".$data['category_id']."'";
		}

		if (!empty($data['filter_title'])) {
			$sql .= " AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}	
	
	
	public function getPostsByCategory($data = array()) {
		/* if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$cache = md5(http_build_query($data));
		
		$post_data = $this->cache->get('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache);
		
	
		if (!$post_data) { */
			
			$sql = "SELECT 
						* 
					FROM 
						" . DB_PREFIX . "blog_posts bp 
						LEFT JOIN " . DB_PREFIX . "blog_posts_description bpd ON (bp.id = bpd.blog_post_id) 
						LEFT JOIN " . DB_PREFIX . "blog_posts_to_blog_categories as bc ON(bc.blog_post_id = bp.id) 
						LEFT JOIN " . DB_PREFIX . "user usr ON (usr.user_id = bp.modified_by)
					WHERE 
						bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
						AND bp.published = '1'
						AND bc.blog_category_id='".$data['category_id']."'";
		
			if (!empty($data['filter_title'])) {
				$sql .= "  AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
			}

			$sort_data = array(
				'bpd.title',
				'bp.published',
				'bp.created',
				'bp.modified'
			);		
		
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY bp.modified";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}		

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			
			$query = $this->db->query($sql);

			$post_data = $query->rows;

			/* $this->cache->set('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache, $post_data);
		}	 */

		return $post_data;			

	}
		
	public function getPosts($data = array()) {

		/* if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$cache = md5(http_build_query($data));
		
		$post_data = $this->cache->get('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache);
		
	
		if (!$post_data) { */
			
			$sql = "SELECT * FROM 
					" . DB_PREFIX . "blog_posts bp 
					LEFT JOIN " . DB_PREFIX . "blog_posts_description bpd ON (bp.id = bpd.blog_post_id) 
					LEFT JOIN " . DB_PREFIX . "user usr ON (usr.user_id = bp.modified_by)
				WHERE 
					bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
					AND  bp.published = '1'";
		
			if (!empty($data['filter_title'])) {
				$sql .= "  AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
			}

			$sort_data = array(
				'bpd.title',
				'bp.published',
				'bp.created',
				'bp.modified'
			);		
		
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY bp.modified";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}		

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			
			$query = $this->db->query($sql);

			$post_data = $query->rows;

			/* $this->cache->set('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache, $post_data);
		}	 */

		return $post_data;			

	}
	
	public function getPost($postId) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
		$query = $this->db->query("
			SELECT 
				bpd.title AS title, 
				bp.modified AS created, 
				bpd.description as description,
				CONCAT(usr.firstname,' ',usr.lastname) as author_name  
			FROM 
				" . DB_PREFIX . "blog_posts bp LEFT JOIN 
				" . DB_PREFIX . "blog_posts_description bpd ON (bp.id = bpd.blog_post_id) LEFT JOIN 
				" . DB_PREFIX . "blog_posts_to_store bp2s ON (bp.id = bp2s.blog_post_id) LEFT JOIN 
				" . DB_PREFIX . "user usr ON (usr.user_id = bp.modified_by)
			WHERE 
				bp.id = '" . (int)$postId . "' 
				AND bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
				AND bp.published = '1' 
				AND bp2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"
		);

		if ($query->num_rows) {	

			/* $comments = $this->db->query("
				SELECT 
					bc.author_name AS name, 
					bc.created AS created, 
					bc.content AS comment 
				FROM 
					" . DB_PREFIX . "blog_comments bc
				WHERE 
					bc.blog_post_id = '" . (int)$postId . "' 
					AND bc.approved = '1' 
					AND bc.published = '1'"
			);
			 */
			$post = array();
			$post = $query->row;
			/* $post['comments'] = $comments->rows; */
			return $post;
		} else {
			return false;
		}
	}
	
	
	
	
	public function getCategories($parent_id = 0) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)");
		
		return $query->rows;
	}

	public function getCategoriesByParentId($category_id) {
		$category_data = array();
		
		$category_query = $this->db->query("SELECT category_id FROM " . DB_PREFIX . "category WHERE parent_id = '" . (int)$category_id . "'");
		
		foreach ($category_query->rows as $category) {
			$category_data[] = $category['category_id'];
			
			$children = $this->getCategoriesByParentId($category['category_id']);
			
			if ($children) {
				$category_data = array_merge($children, $category_data);
			}			
		}
		
		return $category_data;
	}
		
	public function getCategoryLayoutId($category_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category_to_layout WHERE category_id = '" . (int)$category_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");
		
		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return $this->config->get('config_layout_category');
		}
	}
					
	public function getTotalCategoriesByCategoryId($parent_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");
		
		return $query->row['total'];
	}
	
	public function rgetTotalPosts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.id) AS total FROM " . DB_PREFIX . "blog_posts p LEFT JOIN " . DB_PREFIX . "blog_posts_description bpd ON (p.id = bpd.blog_post_id)  LEFT JOIN " . DB_PREFIX . "blog_posts_to_store p2s ON (p.id = p2s.blog_post_id)";

		if (!empty($data['filter_category_id'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "blog_posts_to_blog_categories p2c ON (p.id = p2c.blog_post_id)";			
		}

					
		$sql .= " WHERE bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.published = '1' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
		
		if (!empty($data['filter_name'])) {
			$sql .= " AND (";
								
			if (!empty($data['filter_name'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_name']);
				
				foreach ($words as $word) {
					if (!empty($data['filter_description'])) {
						$implode[] = "LCASE(bpd.title) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(bpd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					} else {
						$implode[] = "LCASE(bpd.title) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					}				
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
			
			if (!empty($data['filter_name'])) {
				$sql .= " OR ";
			}

			$sql .= ")";
		}
		
		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$implode_data = array();
				
				$implode_data[] = "p2c.blog_category_id = '" . (int)$data['filter_category_id'] . "'";
				
				$this->load->model('blog/category');

				$categories = $this->model_blog_category->getCategories($data['filter_category_id']);
					
				foreach ($categories as $category_id) {
					$implode_data[] = "p2c.blog_category_id = '" . (int)$category_id . "'";
				}
							
				$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
			} else {
				$sql .= " AND p2c.blog_category_id = '" . (int)$data['filter_category_id'] . "'";
			}
		}		
				
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
	
	public function getYears(){
		$years 	= $this->db->query("SELECT 
										YEAR(bps.modified) as id, 
										YEAR(bps.modified) as year, 
										count(*) as cnt 
									FROM 
										" . DB_PREFIX . "blog_posts as bps
									WHERE 
										1 
									GROUP BY 
										YEAR(bps.modified) 
									ORDER BY 
										year");

		return $years->rows;
	}
	
	
	public function getMonths($year){
		$months  = $this->db->query("SELECT 
										MONTH(bps.modified) as id,
										MONTHNAME(bps.modified) as month, 
										COUNT(*) AS cnt
									FROM 
										" . DB_PREFIX . "blog_posts as bps
									WHERE 
										YEAR(bps.modified)='".$year."'
									GROUP BY 
										MONTH(bps.modified)
									ORDER BY 
										MONTH(bps.modified)");

		return $months->rows;
	}
	
	
	
	public function getTotalPostsByArchieve($data) {
      	$sql = "";

		if (!empty($data['filter_title'])) {
			$sql = "SELECT 
						COUNT(*) AS total 
					FROM 
						" . DB_PREFIX . "blog_posts as bp 
						LEFT JOIN " . DB_PREFIX . "blog_posts_description as bpd ON(bpd.blog_post_id = bp.id) 
					WHERE 
						bpd.language_id = '" . (int)$this->config->get('config_language_id')."' 
						AND  bp.published = '1'";
		}else{
			$sql = "SELECT 
						COUNT(*) AS total 
					FROM 
						" . DB_PREFIX . "blog_posts as bp 
					WHERE 
						bp.published = '1'";
		}
		
		
		if(isset($data['year']) && (int)$data['year']>12){
			$sql .= " AND YEAR(bp.modified)='".$data['year']."' ";
		}
		
		if(isset($data['month']) && (int)$data['month']>0){
			$sql .= " AND MONTH(bp.modified)='".$data['month']."' ";
		}

		if (!empty($data['filter_title'])) {
			$sql .= " AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
		}
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}	
	
	
	public function getPostsByArchieve($data = array()) {
		/* if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$cache = md5(http_build_query($data));
		
		$post_data = $this->cache->get('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache);
		
	
		if (!$post_data) { */
			
			$sql = "SELECT 
						* 
					FROM 
						" . DB_PREFIX . "blog_posts bp 
						LEFT JOIN " . DB_PREFIX . "blog_posts_description bpd ON (bp.id = bpd.blog_post_id) 
						LEFT JOIN " . DB_PREFIX . "user usr ON (usr.user_id = bp.modified_by)
					WHERE 
						bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
						AND bp.published = '1'";
						
			if(isset($data['year']) && (int)$data['year']>12){
				$sql .= " AND YEAR(bp.modified)='".$data['year']."' ";
			}
			
			if(isset($data['month']) && (int)$data['month']>0){
				$sql .= " AND MONTH(bp.modified)='".$data['month']."' ";
			}
		
			if (!empty($data['filter_title'])) {
				$sql .= "  AND LCASE(bpd.title) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_title'])) . "%'";
			}

			$sort_data = array(
				'bpd.title',
				'bp.published',
				'bp.created',
				'bp.modified'
			);		
		
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY bp.modified";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}		

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			
			$query = $this->db->query($sql);

			$post_data = $query->rows;

			/* $this->cache->set('front_blog_post.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . (int)$customer_group_id . '.' . $cache, $post_data);
		}	 */

		return $post_data;			

	}
}
?>